package DataAccessLayer;

import DTO.Report.ReportList;
import DTO.Report.Reports;
import java.math.BigInteger;
import java.sql.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class ReportsSql extends DataLayerBase {

    public ReportsSql() {
    }

    public Boolean Insert(Reports businessObject) {
        Connection con = getMainConnection();
        String sql = "{call dbo.[Reports_Insert](?,?,?,?,?,?)}";
        CallableStatement cs = null;
        try {
            cs = con.prepareCall(sql);
            cs.registerOutParameter(1, java.sql.Types.INTEGER);
            cs.setInt(2, Integer.parseInt(businessObject.getMemIdRev().toString()));
            cs.setInt(3, Integer.parseInt(businessObject.getMemIdSend().toString()));
            cs.setInt(4, Integer.parseInt(businessObject.getCommission().toString()));
            cs.setString(5, businessObject.getDate());
            cs.setString(6, businessObject.getNote());

            cs.executeUpdate();
            businessObject.setReportId(BigInteger.valueOf((int) cs.getInt(1)));
            return true;
        } catch (Exception ex) {
            return false;
        } finally {
            try {
                cs.close();
                con.close();
            } catch (SQLException ex) {
            }
        }
    }

    public Boolean Update(Reports businessObject) {
        Connection con = getMainConnection();
        CallableStatement cs = null;
        String sql = "{call dbo.[Reports_Update](?,?,?,?,?,?)}";
        try {
            cs = con.prepareCall(sql);
            cs.setInt(1, Integer.parseInt(businessObject.getReportId().toString()));
            cs.setInt(2, Integer.parseInt(businessObject.getMemIdRev().toString()));
            cs.setInt(3, Integer.parseInt(businessObject.getMemIdSend().toString()));
            cs.setInt(4, Integer.parseInt(businessObject.getCommission().toString()));
            cs.setString(5, businessObject.getDate());
            cs.setString(6, businessObject.getNote());

            cs.executeUpdate();
            return true;
        } catch (Exception ex) {
            return false;
        } finally {
            try {
                cs.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(MembersSql.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public Reports SelectByPrimaryKey(int keys) {
        Connection con = getMainConnection();
        CallableStatement cs = null;
        ResultSet rs;
        String sql = "{call dbo.[Reports_SelectByPrimaryKey](?)}";
        try {
            cs = con.prepareCall(sql);
            cs.setInt(1, keys);
            rs = cs.executeQuery();

            if (rs.next()) {
                Reports businessObject = new Reports();

                PopulateBusinessObjectFromReader(businessObject, rs);

                return businessObject;
            } else {
                return null;
            }
        } catch (Exception ex) {
            return null;
        } finally {
            try {
                cs.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(MembersSql.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public List<Reports> SelectAll() {
        Connection con = getMainConnection();
        CallableStatement cs = null;
        ResultSet rs;
        String sql = "{call dbo.[Reports_SelectAll]}";
        try {
            cs = con.prepareCall(sql);

            rs = cs.executeQuery();
            return PopulateObjectsFromReader(rs);
        } catch (Exception ex) {
            return null;
        } finally {
            try {
                cs.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(MembersSql.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public List<Reports> SelectByField(String fieldName, Object value) {

        Connection con = getMainConnection();
        CallableStatement cs = null;
        ResultSet rs;
        String sql = "{call dbo.[Reports_SelectByField](?,"+ value.toString() +")}";
        try {
            cs = con.prepareCall(sql);
            cs.setString(1, fieldName);
            //cs.setObject(2, value);
            rs = cs.executeQuery();
            return PopulateObjectsFromReader(rs);

        } catch (Exception ex) {
            return null;
        } finally {
            try {
                cs.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(MembersSql.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    private void PopulateBusinessObjectFromReader(Reports businessObject, ResultSet rs) {


        try {
            businessObject.setReportId(BigInteger.valueOf(rs.getInt("ReportId")));
        } catch (SQLException ex) {
        }
        try {
            businessObject.setMemIdRev(BigInteger.valueOf(rs.getInt("MemId_Rev")));
        } catch (SQLException ex) {
        }
        try {
            businessObject.setMemIdSend(BigInteger.valueOf(rs.getInt("MemId_Send")));
        } catch (SQLException ex) {
        }
        try {
            businessObject.setCommission(BigInteger.valueOf(rs.getInt("Commission")));
        } catch (Exception ex) {
        }
        try {
            businessObject.setDate(rs.getString("Date").trim());
        } catch (Exception ex) {
        }
        try {
            businessObject.setNote(rs.getString("Note").trim());
        } catch (Exception ex) {
        }
    }

    private List<Reports> PopulateObjectsFromReader(ResultSet rs){
        try {
            ReportList list = new ReportList();
            while (rs.next()) {
                Reports businessObject = new Reports();
                PopulateBusinessObjectFromReader(businessObject, rs);
                list.addReport(businessObject);
            }
            return list.getReport();
        } catch (SQLException ex) {
            return null;
        }
    }
}
